package com.bjpowernode.test.dao;

import com.bjpowernode.test.entity.Emp;
import com.bjpowernode.test.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements EmpDao {

    //查询所有员工信息
    @Override
    public List<Emp> findEmps(Connection conn) {

        String sql = "select * from emp";
        PreparedStatement sta = null ;
        List<Emp> list = new ArrayList<>();
        try{
            sta = conn.prepareStatement(sql);
            ResultSet rs = sta.executeQuery();

            //遍历循环
            while (rs.next()){
                //取出来
                int id = rs.getInt(1);
                String e_name = rs.getString(2);
                int e_age = rs.getInt(3);
                String e_address = rs.getString(4);
                String e_phone = rs.getString(5);

                //将获取的值封装在emp集合中
                Emp e = new Emp(id, e_name, e_age, e_address, e_phone);
                //最终添加到list集合中
                list.add(e);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,sta,null);
        }
        return list;
    }

    //添加员工信息
    @Override
    public void addEmp(Connection conn, Emp emp) {
        String sql = "insert into emp(e_name,e_age,e_address,e_phone) value (?,?,?,?)";
        PreparedStatement sta = null ;
        try{
            sta = conn.prepareStatement(sql);
            //添加参数
            sta.setString(1,emp.getE_name());
            sta.setInt(2,emp.getE_age());
            sta.setString(3,emp.getE_address());
            sta.setString(4,emp.getE_phone());

            sta.executeUpdate();

        }catch( Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,sta,null);
        }
    }

    //根据id查员工信息
    @Override
    public Emp findEmpById(Connection conn, int eid) {
        String sql = "select * from emp where id = ?";
        PreparedStatement sta = null;
        Emp emp = null ;

        try {
            sta = conn.prepareStatement(sql);
            sta.setInt(1,eid);
            // 执行SQL查询语句
            ResultSet rs = sta.executeQuery();
            if(rs.next()){  //只用取一条，取一条能取到的话
                //把它取出来
                int id = rs.getInt(1);
                String e_name = rs.getString(2);
                int e_age = rs.getInt(3);
                String e_address = rs.getString(4);
                String e_phone = rs.getString(5);

                //接下来进行封装。省事的话，参加一个有参构造方法，在student中
                emp = new Emp(id,e_name,e_age,e_address,e_phone);

            }
        } catch ( Exception e) {
            e.printStackTrace();
        }finally {
            //需要关闭，但是只能关闭 sta 的
            JDBCUtils.close(null,sta,null);
        }
        return emp ;
    }

    //保存编辑后的员工信息
    @Override
    public void editEmp(Connection conn, Emp emp) {
        String sql = "update emp set e_name=?,e_age=?,e_address=?,e_phone=? where id=? ";
        PreparedStatement sta = null ;
        try{
            sta = conn.prepareStatement(sql);
            //添加参数
            sta.setString(1,emp.getE_name());
            sta.setInt(2,emp.getE_age());
            sta.setString(3,emp.getE_address());
            sta.setString(4,emp.getE_phone());
            sta.setInt(5,emp.getId());

            sta.executeUpdate();

        }catch( Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,sta,null);
        }
    }

    @Override
    public void deleteEmpById(Connection conn, int eid) {
        String sql = "delete from emp where id = ? ";
        PreparedStatement sta = null;
        try {
            sta = conn.prepareStatement(sql);
            //这次要添加参数了
            sta.setInt(1,eid);

            // 执行SQL查询语句
            sta.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //需要关闭，但是只能关闭 sta 的
            JDBCUtils.close(null,sta,null);
        }
    }
}
